<?php
//统计
namespace app\ausadmin\controller;
use app\ausadmin\controller\Common;
use think\Request;
use think\Db;
use think\Session;
use think\Cookie;
use think\Controller;
use think\Config;
use Godok\Org\Adminauth;

 class Statistics extends Common{

 	public function index(){
 		$times = "thismonth";
 		if(!empty(input('time'))){
 			$times =  input('time');
 		}
 		if($times=='thisyear' || $times=='lastyear'){
 			$day = "'%m'";
 			$unit = '月';
 		}else{
 			$day = "'%d'";
 			$unit = '日';
 		}

    $kaishitime = strtotime(input('starttime'));
    $jieshutime = strtotime(input('endtime'));
    if(!empty($kaishitime) and !empty($jieshutime)){
        $w = " create_time >= $kaishitime and create_time <= $jieshutime ";
        $day= "'%m月%d'";
        $times = '';
        $this->assign('kaishitime',input('starttime'));
        $this->assign('jieshutime',input('endtime'));
        $management = Db::name('leaseorder')
                  ->where(['status'=>2,'is_delete'=>1])
                  ->where($w)
                  ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
                  ->group("unit")
                  ->select();
        $summanagement = Db::name('leaseorder')
                       ->where(['status'=>2,'is_delete'=>1])
                       ->where($w)
                       ->sum('real_amount');
                     
        //echo Db::name('leaseorder')->getlastsql();          
        //用户充值押金
        $recharge = Db::name('rechargeorder')
            ->where(['pay_status'=>1,'is_delete'=>1]) 
            ->where('deposit','neq','')
            ->where('trade_no' ,'neq', '')
            ->where($w)
            ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(id),0) as deposit")
            ->group('unit')
            ->select();  
        $countrecharge = Db::name('rechargeorder')
                  ->where(['pay_status'=>1,'is_delete'=>1]) 
                  ->where('deposit','neq','')
                  ->where('trade_no' ,'neq', '')
                  ->where($w)
                  ->count('id') ;  

         //echo Db::name('recharge')->getlastsql(); var_dump($recharge);die;   
        $refund=Db::name('refund')
          ->alias('a')
          ->join("rechargeorder b",'a.order_no = b.order_no')
          ->where("b.create_time >= $kaishitime and b.create_time <= $jieshutime")
          ->where(['b.pay_status'=>1,'b.is_delete'=>1])
          ->where('b.refunddeposit','neq','')
          ->field("FROM_UNIXTIME(b.create_time,$day) as unit,ifnull(sum(b.real_amount),0) as deposit")
          ->group('unit')
          ->select();
        $sumrefund=Db::name('refund')
                  ->alias('a')
                  ->join("rechargeorder b",'a.order_no = b.order_no')
                  ->where("b.create_time >= $kaishitime and b.create_time <= $jieshutime")
                  ->where('b.refunddeposit','neq','')
                  ->sum('b.real_amount');
        
        //echo Db::name('refund')->getlastsql(); var_dump($refund);die; 
        //运营商商户增长趋势
        $sellers = Db::name('seller')
                ->where(['status'=>1,'is_delete'=>1]) 
                ->where($w)
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(id),0) as seller")
                ->group("unit")
                ->select();
        $countsellers = Db::name('seller')
                      ->where(['status'=>1,'is_delete'=>1]) 
                      ->where($w)
                      ->count('id');
        
        //echo Db::name('seller')->getlastsql(); var_dump($sellers);die;
        //开户
        $openAccount = Db::name('operator')
                     ->where(['is_delete'=>1,'status'=>1])
                     ->where($w)
                     ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(id),0) as account")
                     ->group("unit")
                     ->select();
        $countopenaccount = Db::name('operator')
                           ->where(['is_delete'=>1,'status'=>1])
                           ->where($w)
                           ->count('id');
        

        //echo Db::name('operator')->getlastsql(); var_dump($openAccount);die;
        //开户订单
        $openOrder = Db::name('operator_order')
                   ->where(['pay_status'=>1,'is_delete'=>1])
                   ->where($w)
                   ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
                   ->group('unit')
                   ->select(); 
        $sumopenorder = Db::name('operator_order') 
                      ->where(['pay_status'=>1,'is_delete'=>1])
                      ->where($w) 
                      ->sum('real_amount') ;
                      
        //echo Db::name('openOrder')->getlastsql(); var_dump($openOrder);die;  
        //用户增长趋势
        $users = Db::name('user')
               ->where(['is_delete'=>1]) 
               ->where($w)
               ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(distinct openid),0) as total")
               ->group('unit')
               ->select(); 
        $countusers = Db::name('user')
                    ->where(['is_delete'=>1]) 
                    ->where($w) 
                    ->distinct(true)
                    ->count('distinct openid') ;

         //echo Db::name('user')->getlastsql(); var_dump($users);die;            
         //            
         //              
         //                  

    }else{

 		//运营商经营状况
 		$time = gettimestamp($times);
        $wl['create_time'] = array(array('egt',$time['starttime']),array('elt',$time['endtime']), 'and');

        $management = Db::name('leaseorder')
                  ->where(['status'=>2,'is_delete'=>1])
                  ->where($wl)
                  ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
                  ->group("unit")
                  ->select();
        $summanagement = Db::name('leaseorder')
                       ->where(['status'=>2,'is_delete'=>1])
                       ->where($wl)
                       ->sum('real_amount');
 		//用户充值押金
 		$recharge = Db::name('rechargeorder')
 			      ->where(['pay_status'=>1,'is_delete'=>1])
            ->where('deposit','neq','')	
 			      ->where('trade_no' ,'neq', '')
 			      ->where($wl)
 			      ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(id),0) as deposit")
		    	  ->group('unit')
		    	  ->select();
     $countrecharge = Db::name('rechargeorder')
                  ->where(['pay_status'=>1,'is_delete'=>1]) 
                  ->where('deposit','neq','')
                  ->where('trade_no' ,'neq', '')
                  ->where($wl)
                  ->count('id');	
		//var_dump($recharge);die; 
 		//用户退押金
    $wll["b.create_time"] = array(array('egt',$time['starttime']),array('elt',$time['endtime']), 'and');
 		$refund = DB::name('refund')
            ->alias('a')
            ->join("rechargeorder b",'a.order_no = b.order_no')
            ->where($wll)
            ->where('b.refunddeposit','neq','')
     				->where(['b.pay_status'=>1,'b.is_delete'=>1])
 				    ->field("FROM_UNIXTIME(b.create_time,$day) as unit,ifnull(sum(b.real_amount),0) as deposit")
		    	  ->group('unit')
		    	  ->select();
    $sumrefund=Db::name('refund')
              ->alias('a')
              ->join("rechargeorder b",'a.order_no = b.order_no')
              ->where($wll)
              ->where('b.refunddeposit','neq','')
              ->where(['b.pay_status'=>1,'b.is_delete'=>1])
              ->sum('b.real_amount');
		//var_dump($refund);die;
 		//运营商商户增长趋势
		$sellers = Db::name('seller')
                ->where(['status'=>1,'is_delete'=>1]) 
                ->where($wl)
                ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(id),0) as seller")
                ->group("unit")
                ->select();
    $countsellers = Db::name('seller')
                  ->where(['status'=>1,'is_delete'=>1]) 
                  ->where($wl)
                  ->count('id');
 		//var_dump($sellers);die;
 		//开户
 		$openAccount = Db::name('operator')
 					 ->where(['is_delete'=>1,'status'=>1])
 					 ->where($wl)
 					 ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(id),0) as account")
           ->group("unit")
           ->select();
    $countopenaccount = Db::name('operator')
                     ->where(['is_delete'=>1,'status'=>1])
                     ->where($wl)
                     ->count('id');
		//var_dump($openAccount);die;
 		//开户订单
 		$openOrder = Db::name('operator_order')
 				   ->where(['pay_status'=>1,'is_delete'=>1])
 				   ->where($wl)
 				   ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(sum(real_amount),0) as amount")
		    	 ->group('unit')
		    	 ->select();
    $sumopenorder = Db::name('operator_order') 
                  ->where(['pay_status'=>1,'is_delete'=>1])
                  ->where($wl) 
                  ->sum('real_amount') ;
		//var_dump($openOrder);die;
 		//用户增长趋势
 		$users = Db::name('user')
 			   ->where(['is_delete'=>1]) 
 			   ->where($wl)
 			   ->field("FROM_UNIXTIME(create_time,$day) as unit,ifnull(count(distinct openid),0) as total")
 			   ->group('unit')
		       ->select();
    $countusers = Db::name('user')
                ->where(['is_delete'=>1]) 
                ->where($wl) 
                ->count('distinct openid') ;
    }
    $this->assign('management',$management); 
    $this->assign('recharge',$recharge);
    $this->assign('refund',$refund);
    $this->assign('sellers',$sellers);
    $this->assign('openAccount',$openAccount); 
    $this->assign('openOrder',$openOrder); 
    $this->assign('users',$users);       
 		$this->assign('time',$times);
 		$this->assign('unit',lang($unit));
    $this->assign('summanagement',$summanagement);  
    $this->assign('countrecharge',$countrecharge); 
    $this->assign('sumrefund',$sumrefund);
    $this->assign('countsellers',$countsellers);
    $this->assign('countopenaccount',$countopenaccount);
    $this->assign('sumopenorder',$sumopenorder);
    $this->assign('countusers',$countusers);  
 		return $this->fetch();
 	}
}